﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using MySql.Data.MySqlClient;

namespace JXT.PrimaryKey.Batman.Domain.Repositories.MySql
{
    /// <summary>
    /// 数据库执行对象帮助类
    /// </summary>
    public static class MySqlRepositoryContextExtensions
    {
        /// <summary>
        /// 执行指定的sql查询，返回受影响的行数
        /// </summary>
        /// <param name="context">存储上下文对象</param>
        /// <param name="sql">sql查询语句</param>
        /// <param name="parameters">sql查询参数</param>
        /// <returns>收影响的行数</returns>
        public static int ExecuteNonQuery(this MySqlRepositoryContext context, string sql, IDataParameter[] parameters = null)
        {
            var command = context.CreateCommand();
            command.CommandText = sql;
            if (parameters != null)
                command.Parameters.AddRange(parameters);
            return command.ExecuteNonQuery();
        }

        /// <summary>
        /// 执行指定的sql查询，返回唯一执行结果（或者为第一行第一列）
        /// </summary>
        /// <param name="context">存储上下文对象</param>
        /// <param name="sql">sql查询语句</param>
        /// <param name="parameters">sql查询参数</param>
        /// <returns>唯一执行结果（或者为第一行第一列）</returns>
        public static object ExecuteScalar(this MySqlRepositoryContext context, string sql, IDataParameter[] parameters = null)
        {
            var command = context.CreateCommand();
            command.CommandText = sql;
            if (parameters != null)
                command.Parameters.AddRange(parameters);
            return command.ExecuteScalar();
        }

        /// <summary>
        /// 执行指定的sql查询，返回DataReader对象
        /// </summary>
        /// <param name="context">存储上下文对象</param>
        /// <param name="sql">sql查询语句</param>
        /// <param name="parameters">sql查询参数</param>
        /// <returns>执行得到的DataReader对象</returns>
        public static IDataReader ExecuteDataReader(this MySqlRepositoryContext context, string sql, IDataParameter[] parameters = null)
        {
            var command = context.CreateCommand();
            command.CommandText = sql;
            if (parameters != null)
                command.Parameters.AddRange(parameters);
            return command.ExecuteReader();
        }

        /// <summary>
        /// 执行指定的存储过程，返回受影响的行数
        /// </summary>
        /// <param name="context">存储上下文对象</param>
        /// <param name="procedureName">存储过程</param>
        /// <param name="parameters">存储过程参数</param>
        /// <returns>收影响的行数</returns>
        public static int ExecuteNonQueryProcedure(this MySqlRepositoryContext context, string procedureName, IDataParameter[] parameters = null)
        {
            var command = context.CreateCommand();
            command.CommandText = procedureName;
            command.CommandType = CommandType.StoredProcedure;
            if (parameters != null)
                command.Parameters.AddRange(parameters);
            return command.ExecuteNonQuery();
        }

        /// <summary>
        /// 执行指定的存储过程，返回唯一执行结果（或者为第一行第一列）
        /// </summary>
        /// <param name="context">存储上下文对象</param>
        /// <param name="procedureName">存储过程</param>
        /// <param name="parameters">存储过程参数</param>
        /// <returns>唯一执行结果（或者为第一行第一列）</returns>
        public static object ExecuteScalarProcedure(this MySqlRepositoryContext context, string procedureName, IDataParameter[] parameters)
        {
            var command = context.CreateCommand();
            command.CommandText = procedureName;
            command.CommandType = CommandType.StoredProcedure;
            if (parameters != null)
                command.Parameters.AddRange(parameters);
            return command.ExecuteScalar();
        }

        /// <summary>
        /// 执行指定的存储过程，返回DataReader对象
        /// </summary>
        /// <param name="context">存储上下文对象</param>
        /// <param name="procedureName">存储过程</param>
        /// <param name="parameters">存储过程参数</param>
        /// <returns>执行得到的DataReader对象</returns>
        public static IDataReader ExecuteDataReaderProcedure(this MySqlRepositoryContext context, string procedureName, IDataParameter[] parameters)
        {
            var command = context.CreateCommand();
            command.CommandText = procedureName;
            command.CommandType = CommandType.StoredProcedure;
            if (parameters != null)
                command.Parameters.AddRange(parameters);
            return command.ExecuteReader();
        }

        /// <summary>
        /// 创建数据库查询参数
        /// </summary>
        /// <param name="context">存储上下文对象</param>
        /// <param name="name">参数名</param>
        /// <param name="value">参数值</param>
        /// <param name="size">参数长度限制，可忽略</param>
        /// <param name="dbType">参数类型，可忽略</param>
        /// <returns></returns>
        public static IDataParameter CreateParameter(this MySqlRepositoryContext context, string name, object value, int? size = null, DbType? dbType = null)
        {
            var parameter = new MySqlParameter(name, value);
            if (size != null)
                parameter.Size = size.Value;
            if (dbType != null)
                parameter.DbType = dbType.Value;
            return parameter;
        }

        /// <summary>
        /// 从DataReader读取指定类型的数据
        /// </summary>
        /// <remarks>
        /// 此处类型转换可能出现异常
        /// </remarks>
        /// <typeparam name="T">读取数据类型</typeparam>
        /// <param name="dr">当前DataReader对象</param>
        /// <param name="columnName">列名</param>
        /// <returns>读出的结果</returns>
        public static T Read<T>(this IDataReader dr, string columnName)
        {
            object obj = dr[columnName];
            if (obj == DBNull.Value || obj == null)
                return default(T);
            return ConvertDbValue<T>(obj);
        }

        /// <summary>
        /// 从DataReader读取指定类型的数据
        /// </summary>
        /// <remarks>
        /// 此方法不抛出任何异常
        /// </remarks>
        /// <typeparam name="T">读取数据类型</typeparam>
        /// <param name="dr">当前DataReader对象</param>
        /// <param name="columnName">列名</param>
        /// <returns>读出的结果</returns>
        public static T SafeRead<T>(this IDataReader dr, string columnName)
        {
            try
            {
                return dr.Read<T>(columnName);
            }
            catch
            {
                return default(T);
            }
        }

        /// <summary>
        /// 尝试从DataReader读取数据
        /// </summary>
        /// <typeparam name="T">读取数据类型</typeparam>
        /// <param name="dr">当前DataReader对象</param>
        /// <param name="columnName">列名</param>
        /// <param name="result">读取结果</param>
        /// <returns>操作是否成功</returns>
        public static bool TryRead<T>(this IDataReader dr, string columnName, out T result)
        {
            try
            {
                object obj = dr[columnName];
                if (obj == DBNull.Value || obj == null)
                {
                    result = default(T);
                    return false;
                }
                object value = null;
                var nullableType = typeof(Nullable<>);
                var type = typeof(T);
                if (type == typeof(object))
                {
                    value = obj;
                }
                else if (type.IsGenericType && type.GetGenericTypeDefinition() == nullableType)
                {
                    value = Convert.ChangeType(obj, type.GetGenericArguments()[0]);
                }
                else
                {
                    value = Convert.ChangeType(obj, typeof(T));
                }
                result = (T)value;
                return true;
            }
            catch
            {
                result = default(T);
                return false;
            }
        }

        private static T ConvertDbValue<T>(object obj)
        {
            try
            {
                object result = null;
                var nullableType = typeof(Nullable<>);
                var type = typeof(T);
                if (type.IsGenericType && type.GetGenericTypeDefinition() == nullableType)
                {
                    result = Convert.ChangeType(obj, type.GetGenericArguments()[0]);
                }
                else
                {
                    result = Convert.ChangeType(obj, typeof(T));
                }
                return (T)result;
            }
            catch
            {
                return default(T);
            }
        }
    }
}
